package com.zsrt.oa.dept.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.zsrt.common.entity.Combobox;
import com.zsrt.oa.dept.dao.DeptDao;
import com.zsrt.oa.dept.entity.DeptEntity;
import com.zsrt.utils.DBUtils;

/**
 * 部门数据层的实现类
 * @author 罗智壕-13424431416
 *
 * 2017年6月3日
 */
public class DeptDaoImpl implements DeptDao {
	
	/**
	 * 用户增加下拉框显示部门
	 * @return
	 */
	@Override
	public List<Combobox> deptQuery()  throws SQLException{
		List<Combobox> list = new ArrayList<Combobox>();
		Connection con = DBUtils.getMessage();
		String sql = "select id as deptId,dept_name as deptName from dept";
		PreparedStatement ps = con.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			Combobox box = new Combobox();
			box.setValueField(rs.getString("deptID"));
			box.setTextField(rs.getString("deptName"));
			list.add(box);
		}
		return list;
	}
	
	/**
	 * 部门的增加
	 * @param deptEntity 需要增加的部门对象
	 * @return 返回结果
	 */
	@Override
	public int add(DeptEntity deptEntity) throws SQLException{
		int result = 0;
		Connection con = DBUtils.getMessage();
		String sql = "insert into DEPT values(DEPT_S.nextval,?)";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, deptEntity.getDeptName());
		result = ps.executeUpdate();
		DBUtils.closeResources(con, ps);
		return result;
	}
	
	/**
	 * 查询全部部门
	 * @param pageSize 从第几页开始
	 * @param curPage 到第几页结束
	 * @return 返回结果
	 * @throws SQLException
	 */
	@Override
	public List<DeptEntity> query(int pageSize,int curPage,Map<String,Object> map) throws SQLException {
		List<DeptEntity> list = new ArrayList<DeptEntity>();
		Connection con = DBUtils.getMessage();
		String sql = "SELECT GET.ID AS deptId,GET.dept_name AS deptName FROM "
				+ "(SELECT ID,dept_name,ROWNUM AS ROW_NUM FROM dept L)GET "
				+ "WHERE GET.ROW_NUM>=? AND GET.ROW_NUM<=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, pageSize);
		ps.setInt(2, curPage);
		
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()){
			DeptEntity entity = new DeptEntity();
			entity.setDeptId(rs.getString("deptId"));
			entity.setDeptName(rs.getString("deptName"));
			list.add(entity);
		}
		
		
		String sql2 = "select count(*)/3 as cout from dept";
		ps = con.prepareStatement(sql2);
		ResultSet rsCount = ps.executeQuery();
		while(rsCount.next()){
			String count = rsCount.getString("cout");
			boolean result = DBUtils.isInteger(count);
			if(result){
				map.put("count", count);
			}else{
				Double dou = Double.valueOf(count);
				if(dou >= dou && dou <= dou+1){
					Integer integer = (int) (dou+1);
					count = String.valueOf(integer);
				}
				map.put("count", count);
			}
		}
		
		DBUtils.closeResources(con, ps, rs);
		return list;
	}
	
	/**
	 * 删除部门
	 * @param deptEntity
	 * @return
	 * @throws SQLException
	 */
	@Override
	public void delete(DeptEntity deptEntity) throws SQLException {
		String deptId = deptEntity.getDeptId();
		
		Connection con = DBUtils.getMessage();
		PreparedStatement ps = null;
		String[] deptIdArrays = deptId.split(",");
		String sql = "delete from dept where id = ?";
		ps = con.prepareStatement(sql);
		for(int index=0; index<deptIdArrays.length; index++){
			ps.setString(1, deptIdArrays[index]);
			ps.addBatch();
		}
		ps.executeBatch();
	}
	
	/**
	 * 修改填充
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List<DeptEntity> updateQuery(DeptEntity entity) throws SQLException {
		List<DeptEntity> list = new ArrayList<DeptEntity>();
		Connection con = DBUtils.getMessage();
		String sql = "select id as deptId,dept_name as deptName from dept where id = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, entity.getDeptId());
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			entity.setDeptId(rs.getString("deptId"));
			entity.setDeptName(rs.getString("deptName"));
			list.add(entity);
		}
		return list;
	}
	
	/**
	 * 修改
	 * @param entity
	 * @throws SQLException
	 */
	@Override
	public int update(DeptEntity entity) throws SQLException {
		Connection con = DBUtils.getMessage();
		String sql = "update dept set dept_name = ? where id = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, entity.getDeptName());
		ps.setString(2, entity.getDeptId());
		int result = ps.executeUpdate();
		return result;
	}
}
